﻿using Microsoft.Extensions.Options;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using Yichat.MicroService.Interface;
using Yichat.MicroService.Model;
using Dapper;

namespace Yichat.MicroService.Service
{
    public class SqlLiteDatabaseCache : IDistributeionCache
    {
        private readonly string _connectionString;
        private readonly string _schemaName;
        private readonly string _tableName;

        public SqlLiteDatabaseCache(IOptions<SqlLiteCacheOptions> options)
        {
            var cacheOptions = options.Value;
            _connectionString = cacheOptions.ConnectionString;
            _schemaName = cacheOptions.SchemaName;
            _tableName = cacheOptions.TableName;
        }

        public async Task<bool> ExistsAsync(string key, CancellationToken token = default)
        {
            await DeleteExpiredCacheItems();
            using (var connection = new System.Data.SQLite.SQLiteConnection(_connectionString))
            {
                var sql = $"select * from {_tableName} where [key]=@key";
                var cacheItem = await connection.QueryFirstOrDefaultAsync<CacheItem>(sql, new { key = key });
                var raw = cacheItem?.ToCacheEntry<dynamic>();
                if (raw == null || raw.IsExpired)
                {
                    return false;
                }

                return true;
            }
        }

        public async Task<TValue> GetAsync<TValue>(string key, TValue defaultValue = default, CancellationToken token = default)
        {
            await DeleteExpiredCacheItems();
            using (var connection = new System.Data.SQLite.SQLiteConnection(_connectionString))
            {
                var sql = $"select * from {_tableName} where [key]=@key; update {_tableName} set LastAccessUtc=datetime('now') where [key]=@key";
                var cacheItem = await connection.QueryFirstOrDefaultAsync<CacheItem>(sql, new { key = key });
                var raw = cacheItem?.ToCacheEntry<TValue>();
                if (raw == null || raw.IsExpired)
                {
                    return defaultValue;
                }

                return raw.Value;
            }
        }

        public async Task RemoveAsync<TValue>(string key, CancellationToken token = default)
        {
            using (var connection = new System.Data.SQLite.SQLiteConnection(_connectionString))
            {
                var sql = $"delete from {_tableName} where [key]=@key";
                await connection.ExecuteAsync(sql, new { key = key });
            }
            await DeleteExpiredCacheItems();
        }

        public async Task SetAsync<TValue>(string key, TValue value, TimeSpan? slidingExpiration = null, DateTimeOffset? absoluteExpiration = null, CancellationToken token = default)
        {
            var isExists = await ExistsAsync(key);
            var sql = $"update {_tableName} set [Value]=@Value,CreatedUtc=@CreatedUtc,CacheExpireStategy=@CacheExpireStategy,AbsoluteExpiration=@AbsoluteExpiration,SlidingExpiration=@SlidingExpiration,LastAccessUtc=@LastAccessUtc where [key]=@key";
            if (!isExists)
            {
                sql = $"insert into {_tableName}([key],[Value],CreatedUtc,CacheExpireStategy,AbsoluteExpiration,SlidingExpiration,LastAccessUtc) values(@Key,@Value,@CreatedUtc,@CacheExpireStategy,@AbsoluteExpiration,@SlidingExpiration,@LastAccessUtc)";
            }

            using (var connection = new System.Data.SQLite.SQLiteConnection(_connectionString))
            {
                var cacheEntry = new DistributeionCacheEntry<TValue>(key, value, slidingExpiration, absoluteExpiration);
                var cacheItem = cacheEntry.FromCacheEntry<TValue>();
                await connection.ExecuteAsync(sql, cacheItem);
            }

            await DeleteExpiredCacheItems();
        }

        private async Task DeleteExpiredCacheItems()
        {
            using (var connection = new System.Data.SQLite.SQLiteConnection(_connectionString))
            {
                var sql = $"delete from {_tableName} where (CacheExpireStategy=1 and AbsoluteExpiration < datetime('now')) or (CacheExpireStategy=2 and SlidingExpiration<(LastAccessUtc-datetime('now'))*1000)";
                await connection.ExecuteAsync(sql);
            }
        }
    }
}
